package com.ysd.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.ysd.entity.Statistcs;

public interface StatistcsRepository extends JpaRepository<Statistcs, Integer> {
		//根据资源室排序
	@Query(value = "select s.read_romm_id,r.read_room_name ,SUM(num) from statistcs s join read_room r on s.read_romm_id=r.read_romm_id GROUP BY  s.read_romm_id", nativeQuery = true)
	@Modifying 
	@Transactional 
	public List<Object> getStatis();
	  
	//根据人统计
	@Transactional
	@Modifying
	@Query(value = "select count(card_id) ,m.name  from consume_log c  join memeber m on c.card_id=m.card GROUP BY card_id",nativeQuery = true)
	public List<Object> getStatisName();
	//根据系统计
	@Transactional
	@Modifying
	@Query(value = "select count(department) as cishu,m.department  from consume_log c join memeber m on c.card_id=m.card GROUP BY department",nativeQuery = true)
	public List<Object> getstatisdepartment();
	
	//根据专业
	@Transactional
	@Modifying
	@Query(value = "SELECT count(profession) as cishu,m.profession from consume_log c join memeber m on c.card_id=m.card GROUP BY profession",nativeQuery = true)
	public List<Object> getStatisZhuanYe();
	
	//根据年级
	@Transactional
	@Modifying
	@Query(value = "select count(grade) as cishu,m.grade	from consume_log c join memeber m on c.card_id=m.card GROUP BY grade",nativeQuery = true)
	public List<Object> getStatisNianJi();
}
